This file describes the custom expression building facility. The facility consists of two parts. The Data_bld program and the expression builder programs. 1.Data_bld program. The Data_bld.prg is used by the developer to create the necessary data files (DBF) if they have not been created. Those DBF files are Q_DATA,Q_EXPR,Q_PHRASE and a file to be named by the developer that contains the field lookup data for the expression builder picklist. Q_DATA.DBF is a data dictionary that the data_bld.prg builds from the current environment. Fields from the master data base and all related data bases (directly related or indirectly related) are included. Unrelated data files are not included. When the data_bld program is started, the developer is given a chance to re-label the data base names for use in expression picklists. This should make it easier for the user to understand what they are picking. Example: Re-label CUST(DBF) to Customer. The developer is given a chance to change (browse) the Field_label and the picture clause for each field used in the expression builder. Fields may also be removed from the list by deleting them. This will remove unnecessary fields or fields that would be confusing to the user. Structure for database : q_data.dbf Field Field Name Type Width Dec 1 FIELD_NAME Character 20 2 FIELDLABEL Character 30 3 FIELD_TYPE Character 1 4 FIELD_LEN Numeric 3 0 5 FIELD_PICT Character 25 6 FIELD_DEC Numeric 3 0 ** Total ** 00083 To re-edit the Q_data data dictionary just rerun the Data_bld program. The Data_bld.prg will continue to use the existing q_data.dbf. This allows correction of mistakes in picture clauses and field labels without having to start over again. (NOTE- Mistakes in picture clauses is the most likely cause for the expression builder to bomb.) To make a new picklist program and data file for another view/environment, just erase or rename the q_data.dbf and open the new files before running the Data_bld program. Be sure to set the file relations. Also, Data_bld.prg will write a program file (named by the developer) that will contain custom code for picklist menus for that particular application environment, and create a data file (DBF) that contains the data necessary for a picklist. The data file for the picklist will be given the same name as the written program so the developer will know which picklist program goes with which data_file. Example: Program= "QCUSTOM.PRG" DBF= "QCUSTOM.DBF") The custom datafile will have the same structure as q_data. One of the parameters for the written program is "data_pop". If data_pop is passed as true(.t.), the program will use code that uses DEFINE POPUP FIELD for the picklist. If you prefer to uses array style menus(i.e.... not enough open work areas), pass data_pop as false (.f.). Depending on the size of the data files in the view, you may have to increase the MVARSIZ in Config.FP. Large DBFs create large memory consuming arrays. The expression builder requires a minimum of 2 open workareas for Q_EXPR and Q_PHRASE. It requires a 3rd workarea if you use DEFINE POPUP FIELD (DATA_POP=.t.) for the picklist. The default value for data_pop is true. To change the data_pop value- change "dbf_pop = .t." to "dbf_pop = .f." in the expr_bld procedure. dbf_pop=.t. && if .t. use DEFINE POPUP FIELD picklist do (qfile) with d_name,dlabel,d_pict,d_type,d_len,d_dec,dbf_pop The Q_EXPR and Q_PHRASE data files contain the expression information. Each record in q_expr contains the information on one expression. The expression values (phrases) for one expression are contained in 1 or more q_phrase records. Of particular interest in the q_expr DBF is the field entitled "PROGRAM". This field is used to tell the expresion builder the current environment. It is passed as a parameter to the main program ("EXPRMENU.PRG"). Examples: my_expr=exprmenu('REPORT','QCUSTOM'). my_expr=exprmenu(PROGRAM(),'QCUSTOM'). NOTE: The 2nd parameter is the name of the custom program created by the data_bld program that contains the picklist for that environment. The q_expr DBF (and q_phrase.dbf) can store the expressions for an entire application, but the "PROGRAM" field tells the expression builder which expressions are appropriate for that particular environment and does not show the user the other expressions. HINT: A USER ID could be passed as the 1st parameter. This would allow each user of the application to have their own set of selection criteria. The parameter must be no more than 8 characters long. If there are multiple environments in the application, then perhaps part of a USER ID plus enough of a character string to identify the environment. Example: USER ID="1234" Environment="Reports" Parameter="1234_REP" Structure for database : q_expr.dbf Field Field Name Type Width Dec 1 EXPR_ID Character 6 2 EXPR_NAME Character 40 3 PROGRAM Character 8 && identifies environment of && calling program 4 FOX_EXPR Logical 1 && is it a getexpr expression 5 IG_CASE Logical 1 && ignore case for expression 6 EXPR Memo 10 && store getexpr values ** Total ** 00067 Structure for database : q_phrase.dbf Field Field Name Type Width Dec 1 EXPR_ID Character 6 2 PHRASE_NO Numeric 2 0 3 OPERATOR Character 2 4 CONNECTOR Character 3 5 Q_FIELD Character 20 6 Q_FLABEL Character 30 7 Q_FTYPE Character 1 8 Q_FLEN Numeric 3 0 9 Q_FPICT Character 25 10 Q_FDEC Numeric 3 0 11 Q_DATA Character 120 12 QD_CHOICE Numeric 1 0 13 QM_CHOICE Numeric 1 0 14 QY_CHOICE Numeric 1 0 15 QD_EXPR Character 30 16 QM_EXPR Character 30 17 QY_EXPR Character 30 ** Total ** 00309 2.Expression builder programs. Exprmenu.prg- main program for expression builder ** The following programs have been bound into the Exprmenu program. Expr_bld.prg- add or edit expressions Expr_get.prg- builds complete expression from q_phrase records Qoperate.prg- displays expression operators and returns selected operator Op2phra.prg- converts operator into a phrase Example: "==" to "is the same as" Phra2op.prg- converts phrases to operator symbols Q_date.prg- builds variable date expression In the interest of speed, The EXPRMENU program does not maintain it's own indexes. Therefore, you will want to place the following code in the program you use that rebuilds your application indexes. USE Q_EXPR INDEX ON PROGRAM+UPPER(EXPR_NAME) TO Q_EXPR INDEX ON EXPR_ID TO Q_ID USE USE Q_PHRASE INDEX ON EXPR_ID+STR(1000+PHRASE_NO) TO Q_PHRASE USE Also, indexes are created for the custom picklist DBFs. They may need reindexing if problems arise. The indexes use the same name as the custom DBF. Examples: USE QCUSTOM INDEX ON FIELDLABEL TO QCUSTOM USE INVOICE INDEX ON FIELDLABEL TO QINVOICE USE HOW TO USE THE EXPRESSION BUILDER 1. Open the data files and set up the relations 2. Make sure the master data base is selected. USE PATIENT INDEX ID USE ADDRESS INDEX ADD_ID IN 2 SET RELATION TO ADD_ID INTO ADDRESS GO TOP DO DATA_BLD 3. Data_bld.prg will show the data file names ("PATIENT","ADDRESS") and allow you to re-label them for the expression builder picklist. 4. Data_bld.prg will build the data dictionary Q_DATA.DBF from all fields in the patient and address files. 5. Then you will be able to browse q_data and change the field_labels and picture clauses for each field if desired, and delete unwanted fields. These changes DO NOT affect the actual data files. 6. It will then call the foxpro putfile() facility so you can name the picklist program and data file. The default name supplied will be the master DBF file name with a "Q" prefix. Example: "QPATIENT" 7. The custom picklist program and data file are created. 8. Now you are ready to insert the expression builder into your programs. 9. Be sure to look over the HELP topics when you start the exprmenu program. ********************************************************** ******* SAMPLE PROGRAM ********************************************************** SET TALK OFF SET STATUS OFF ** supply your files here USE PATIENT INDEX ID USE ADDRESS INDEX ADD_ID IN 2 SET RELATION TO ADD_ID INTO ADDRESS ** GO TOP E_NAME='' && optional parameter that can pass the name of the selected && expression back to the calling program ** allow e_name to receive selected expression name from expremenu.prg. SET UDFPARMS TO REFERENCE MY_EXPR=EXPRMENU(PROGRAM(),'QPATIENT',E_NAME) SET FILTER TO &MY_EXPR GO TOP BROWSE IF '' <> MY_EXPR COUNT TO HOW_MANY FOR &MY_EXPR WAIT 'COUNT FOR '+ALLTRIM(E_NAME) +' IS '+ ; ALLTRIM(STR(HOW_MANY)) + '. PRESS ANY KEY .. ' WINDOW ENDIF CLOSE DATABASES RETURN ************************************** COMMAND LINE TEST ************************************** ** use this to see the actual code that is returned from exprmenu.prg ** xxxx is a substitute parameter. Be sure to use the same parameters when you test it ** It doesn't have to be xxxx. Any character string will do. ** Q?????? is the name of the picklist program you created with Data_bld xx=MY_EXPR=EXPRMENU('XXXX','Q??????') ? xx